﻿using DataBaseTools.Common;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Runtime.InteropServices;
using System.Threading;
using System.Windows;
using System.Windows.Controls;
using System.Xml.Linq;

namespace DataBaseTools
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        string ip = "";
        string userid = "";
        string pwd = "";

        /// <summary>
        /// 窗体初始化
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            DefaultListView();
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            ip = TxtIP.Text.Trim();
            userid = TxtUSER.Text.Trim();
            pwd = TxtPWD.Password.Trim();
            CmbDataBase.Items.Clear();
            List<ComboBoxItem> list = new List<ComboBoxItem>();
            try
            {
                switch (Convert.ToInt32((CmbDataBaseType.SelectedItem as ComboBoxItem).Tag))
                {
                    case 0://SQL Server
                        string conn = $"Data Source={TxtIP.Text.Trim()};Initial Catalog=master;User ID={TxtUSER.Text.Trim()};Password={TxtPWD.Password.Trim()};Connect Timeout=5;";
                        SqlConnection Connection = new SqlConnection(conn);
                        DataTable table = new DataTable();
                        SqlDataAdapter Adapter = new SqlDataAdapter("select name from master..sysdatabases", Connection);
                        Adapter.Fill(table);
                        foreach (DataRow item in table.Rows)
                        {
                            ComboBoxItem cbi = new ComboBoxItem();
                            cbi.Content = item["name"];
                            list.Add(cbi);
                        }
                        list[0].IsSelected = true;
                        break;
                    case 1://Oracle
                        break;
                    case 2://MySql
                        string connection = $"server={TxtIP.Text.Trim()};user id=root;password={TxtPWD.Password.Trim()};database=test; pooling=true;";
                        MySqlConnection mysqlconn = new MySqlConnection(connection);
                        DataTable mysqltable = new DataTable();
                        mysqlconn.Open();
                        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter("SELECT SCHEMA_NAME name FROM INFORMATION_SCHEMA.SCHEMATA", mysqlconn);
                        mySqlDataAdapter.Fill(mysqltable);
                        foreach (DataRow item in mysqltable.Rows)
                        {
                            ComboBoxItem cbi = new ComboBoxItem();
                            cbi.Content = item["name"];
                            list.Add(cbi);
                        }
                        list[0].IsSelected = true;

                        break;
                    case 3://Access
                        break;
                    case 4://MongoDB
                        break;
                }
                list.ForEach(a => CmbDataBase.Items.Add(a));
                CkWLJ.IsChecked = false;
                CkYLJ.IsChecked = true;
                CmbDataBase.SelectionChanged += CmbDataBase_SelectionChanged;
                QueryTable(list[0].Content.ToString());
            }
            catch (Exception ex)
            {
                CmbDataBase.SelectionChanged -= CmbDataBase_SelectionChanged;
                CmbDataBase.Items.Add(new ComboBoxItem() { Content = "请选择...", IsSelected = true });
                CkWLJ.IsChecked = true;
                CkYLJ.IsChecked = false;
                CmbTable.Items.Clear();
                CmbTable.Items.Add(new ComboBoxItem() { Content = "请选择...", IsSelected = true });
                MessageBox.Show("数据库连接失败！原因：" + ex.Message, "提示", MessageBoxButton.OK);
            }


        }

        /// <summary>
        /// 选择数据库查询表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void CmbDataBase_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (e.AddedItems.Count == 0) return;
            QueryTable(((ContentControl)((object[])e.AddedItems)[0]).Content.ToString());
        }

        /// <summary>
        /// 数据表更新事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void CmbTable_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (e.AddedItems.Count == 0) return;
            string data = ((ContentControl)((object[])e.AddedItems)[0]).Content.ToString();
            TxtNameSpace.Text = "";
            if (string.IsNullOrWhiteSpace(data) || data.Contains("请选择"))
                return;
            TxtNameSpace.Text = (((sender as ComboBox).SelectedItem) as ComboBoxItem).Content.ToString().Split('-')[0];
            TvDataColumn.Items.Clear();
            List<CheckBox> list = new List<CheckBox>();
            switch (Convert.ToInt32((CmbDataBaseType.SelectedItem as ComboBoxItem).Tag))
            {
                case 0://SQL Server
                    string conn = $"Data Source={ip};Initial Catalog={(CmbDataBase.SelectedItem as ComboBoxItem).Content.ToString().Trim()};User ID={userid};Password={pwd};Connect Timeout=5;";
                    SqlConnection Connection = new SqlConnection(conn);
                    DataTable table = new DataTable();
                    String sqlServer = $@"SELECT fieldName = a.name,fieldComment = isnull(g.[value],''),
                        fieldType = b.name,fieldLength = COLUMNPROPERTY(a.id,a.name,'PRECISION'),fieldPrecision = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
                        IsEmpty = case when a.isnullable=1 then 1 else 0 end FROM syscolumns a
                        left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
                        left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
                        where d.name='{data.Split('-')[0]}'order by a.id,a.colorder";
                    SqlDataAdapter Adapter = new SqlDataAdapter(sqlServer, Connection);
                    Adapter.Fill(table);
                    if (table.Rows.Count > 0)
                    {
                        foreach (DataRow item in table.Rows)
                        {
                            CheckBox cbox = new CheckBox();
                            cbox.Content = item["fieldName"] + (!string.IsNullOrWhiteSpace(item["fieldComment"].ToString()) ? "-" + item["fieldComment"] : "");
                            cbox.IsChecked = true;
                            cbox.Tag = item;
                            list.Add(cbox);
                        }
                    }
                    break;
                case 1://Oracle
                    break;
                case 2://MySql
                    string connection = $"server={TxtIP.Text.Trim()};user id=root;password={TxtPWD.Password.Trim()};database={(CmbDataBase.SelectedItem as ComboBoxItem).Content.ToString().Trim()}; pooling=true;";
                    MySqlConnection mysqlconn = new MySqlConnection(connection);
                    DataTable mysqltable = new DataTable();
                    mysqlconn.Open();
                    string sql = $@"SELECT COLUMN_NAME fieldName,COLUMN_COMMENT fieldComment FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{data}'";
                    MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, mysqlconn);
                    mySqlDataAdapter.Fill(mysqltable);
                    if (mysqltable.Rows.Count > 0)
                    {
                        foreach (DataRow item in mysqltable.Rows)
                        {
                            CheckBox cbox = new CheckBox();
                            cbox.Content = item["fieldName"] + (!string.IsNullOrWhiteSpace(item["fieldComment"].ToString()) ? "-" + item["fieldComment"] : ""); ;
                            cbox.IsChecked = true;
                            cbox.Tag = item;
                            list.Add(cbox);
                        }
                    }

                    break;
                case 3://Access
                    break;
                case 4://MongoDB
                    break;
            }
            list.ForEach(a => TvDataColumn.Items.Add(a));
            Button_Click_1(null, null);
        }

        /// <summary>
        /// 全选
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click_3(object sender, RoutedEventArgs e)
        {
            SetDataColumnCheck(true);
        }

        /// <summary>
        /// 取消全选
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click_4(object sender, RoutedEventArgs e)
        {

            SetDataColumnCheck(false);
        }

        /// <summary>
        ///导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click_2(object sender, RoutedEventArgs e)
        {
            try
            {
                if (LvEntityView.Items != null && LvEntityView.Items.Count > 0)
                {
                    List<string> lst = new List<string>();
                    foreach (ListViewItem item in LvEntityView.Items)
                    {
                        string str = item.Content.ToString();
                        lst.Add(str);
                    }
                    string filePath = string.Empty;
                    System.Windows.Forms.FolderBrowserDialog fbd = new System.Windows.Forms.FolderBrowserDialog();
                    fbd.Description = "请选择文件夹";

                    if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    {
                        if (string.IsNullOrEmpty(fbd.SelectedPath))
                        {
                            MessageBox.Show("文件夹路径不能为空！", "提示");
                            return;
                        }
                        filePath = fbd.SelectedPath;//存储文件的路径信息
                    }
                    string fileName = this.CmbTable.Text.Split('-')[0];
                    filePath += "\\" + fileName + ".cs";
                    using (StreamWriter sw = File.CreateText(filePath))
                    {
                        foreach (var item in lst)
                        {
                            //if (item.Contains("NameSpace") && !string.IsNullOrEmpty(this.TxtNameSpace.Text.Trim()))
                            //{
                            //    string str = item.Replace("NameSpace", this.TxtNameSpace.Text.Trim());
                            //    sw.WriteLine(str);
                            //    continue;
                            //}
                            sw.WriteLine(item);
                        }
                    }
                }
                else
                {
                    MessageBox.Show("实体类不可为null", "提示", MessageBoxButton.OK);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK);
            }
        }

        /// <summary>
        /// 生成
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            if (CkYLJ.IsChecked != true) return;
            try
            {
                List<CheckBox> ls_cb = new List<CheckBox>();
                foreach (var item in TvDataColumn.Items)
                {
                    if ((item as CheckBox).IsChecked == true)
                    {
                        ls_cb.Add(item as CheckBox);
                    }
                }
                if (ls_cb.Count == 0)
                {
                    DefaultListView();
                }
                else
                {
                    LvEntityView.Items.Clear();
                    string tableName = "";
                    string tableComment = "";
                    string[] table = CmbTable.Text.Split('-');
                    tableName = table[0];
                    if (table.Length > 1)
                        tableComment = table[1];
                    else
                        tableComment = table[0];
                    List<ListViewItem> ls_lvi = new List<ListViewItem>();
                    LvEntityView.Items.Add(new ListViewItem() { Content = "using System;" });
                    LvEntityView.Items.Add(new ListViewItem() { Content = "using System.Collections.Generic;" });
                    LvEntityView.Items.Add(new ListViewItem() { Content = "using System.Text;" });
                    LvEntityView.Items.Add(new ListViewItem() { Content = "" });
                    if (string.IsNullOrWhiteSpace(TxtNameSpace.Text.Trim()))
                        LvEntityView.Items.Add(new ListViewItem() { Content = "namespace NameSpace" });
                    else
                        LvEntityView.Items.Add(new ListViewItem() { Content = "namespace " + TxtNameSpace.Text.Trim() });
                    LvEntityView.Items.Add(new ListViewItem() { Content = "{" });
                    if (!string.IsNullOrWhiteSpace(tableComment))
                    {
                        LvEntityView.Items.Add(new ListViewItem() { Content = "    /// <summary>" });
                        LvEntityView.Items.Add(new ListViewItem() { Content = $"    /// {tableComment}" });
                        LvEntityView.Items.Add(new ListViewItem() { Content = "    /// <summary>" });
                    }
                    LvEntityView.Items.Add(new ListViewItem() { Content = $"    public class {tableName}" });
                    LvEntityView.Items.Add(new ListViewItem() { Content = "    {" });
                    foreach (CheckBox item in ls_cb)
                    {
                        DataRow dr = item.Tag as DataRow;
                        LvEntityView.Items.Add(new ListViewItem() { Content = "        /// </summary>" });
                        LvEntityView.Items.Add(new ListViewItem() { Content = $"        /// {dr["fieldComment"]}" });
                        LvEntityView.Items.Add(new ListViewItem() { Content = "        /// </summary>" });
                        LvEntityView.Items.Add(SetDataType(dr));
                        LvEntityView.Items.Add(new ListViewItem() { Content = "" });
                    }
                    LvEntityView.Items.Add(new ListViewItem() { Content = "    }" });
                    LvEntityView.Items.Add(new ListViewItem() { Content = "}" });

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK);
            }
        }

        /// <summary>
        /// 查询数据库表
        /// </summary>
        private void QueryTable(string dataBase)
        {
            if (CmbTable == null) return;
            CmbTable.Items.Clear();
            List<ComboBoxItem> list = new List<ComboBoxItem>();
            try
            {

                switch (Convert.ToInt32((CmbDataBaseType.SelectedItem as ComboBoxItem).Tag))
                {
                    case 0://SQL Server
                        string conn = $"Data Source={ip};Initial Catalog={dataBase};User ID={userid};Password={pwd};Connect Timeout=5;";
                        SqlConnection Connection = new SqlConnection(conn);
                        DataTable table = new DataTable();
                        string sql = @"select a.name,b.value from sysobjects a left join sys.extended_properties b on a.id = b.major_id and b.minor_id = 0 
                                       where a.xtype='U' and a.name<>'dtproperties' order by a.name";
                        SqlDataAdapter Adapter = new SqlDataAdapter(sql, Connection);
                        Adapter.Fill(table);
                        if (table.Rows.Count > 0)
                        {
                            foreach (DataRow item in table.Rows)
                            {
                                ComboBoxItem cbi = new ComboBoxItem();
                                cbi.Content = item["name"] + (!string.IsNullOrWhiteSpace(item["value"].ToString()) ? "-" + item["value"] : "");
                                list.Add(cbi);
                            }
                        }
                        else
                        {
                            list.Add(new ComboBoxItem() { Content = "请选择...", IsSelected = true });
                        }
                        list[0].IsSelected = true;
                        list.ForEach(a => CmbTable.Items.Add(a));
                        break;
                    case 1://Oracle
                        break;
                    case 2://MySql
                        string connection = $"server={TxtIP.Text.Trim()};user id=root;password={TxtPWD.Password.Trim()};database={dataBase}; pooling=true;";
                        MySqlConnection mysqlconn = new MySqlConnection(connection);
                        DataTable mysqltable = new DataTable();
                        mysqlconn.Open();
                        string sql1 = $"SELECT table_name FROM information_schema.tables WHERE table_schema='{dataBase}' ";
                        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql1, mysqlconn);
                        mySqlDataAdapter.Fill(mysqltable);
                        if (mysqltable.Rows.Count > 0)
                        {
                            foreach (DataRow item in mysqltable.Rows)
                            {
                                ComboBoxItem cbi = new ComboBoxItem();
                                cbi.Content = item["table_name"];
                                CmbTable.Items.Add(cbi);
                            }
                        }
                        else
                        {
                            CmbTable.Items.Add(new ComboBoxItem() { Content = "请选择...", IsSelected = true });
                        }
                        (CmbTable.Items[0] as ComboBoxItem).IsSelected = true;
                        break;
                    case 3://Access
                        break;
                    case 4://MongoDB
                        break;
                }
            }
            catch (Exception)
            {
                list.Add(new ComboBoxItem() { Content = "请选择...", IsSelected = true });
            }
            //CmbTable.ItemsSource = list;
            //
        }

        /// <summary>
        /// 默认ListView
        /// </summary>
        /// <returns></returns>
        private void DefaultListView()
        {
            LvEntityView.Items.Clear();
            LvEntityView.Items.Add(new ListViewItem() { Content = "using System;" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "using System.Collections.Generic;" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "using System.Text;" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "namespace NameSpace" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "{" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "    /// <summary>" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "    /// 表名" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "    /// <summary>" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "    public class 类名" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "        /// </summary>" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "        /// 列备注" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "        /// </summary>" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "        public string MyProperty { get; set; }" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "    }" });
            LvEntityView.Items.Add(new ListViewItem() { Content = "}" });
        }

        /// <summary>
        /// 设置数据列全选/取消全选
        /// </summary>
        private void SetDataColumnCheck(bool flag)
        {
            if (TvDataColumn.Items.Count == 0) return;
            foreach (var item in TvDataColumn.Items)
            {
                (item as CheckBox).IsChecked = flag;
            }
        }

        /// <summary>
        /// 设置数据类型
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private ListViewItem SetDataType(DataRow dr)
        {
            ListViewItem lvt = new ListViewItem();
            string isEmpty = Convert.ToInt32(dr["IsEmpty"]) > 0 ? "?" : "";
            string str = "";
            switch (dr["fieldType"].ToString().ToLowerInvariant())
            {
                case "bigint":
                case "rowversion":
                    str = $"Int64{isEmpty}";
                    break;
                case "int":
                case "smallint":
                case "tinyint":
                case "timestamp":
                    str = $"int{isEmpty}";
                    break;
                case "real":
                case "double":
                case "money":
                case "smallmoney":
                    str = $"double{isEmpty}";
                    break;
                case "float":
                    str = $"float{isEmpty}";
                    break;
                case "decimal":
                case "numeric":
                    str = $"decimal{isEmpty}";
                    break;
                case "char":
                case "varchar":
                case "nchar":
                case "nvarchar":
                case "text":
                case "ntext":
                case "uniqueidentifier":
                    str = $"string";
                    break;
                case "date":
                case "time":
                case "datetime":
                case "datetime2":
                case "smalldatetime":
                case "datetimeoffset":
                    str = $"DateTime{isEmpty}";
                    break;
                case "image":
                    str = $"Image";
                    break;
                case "bit":
                    str = $"bool{isEmpty}";
                    break;
                case "binary":
                case "varbinary":
                    str = $"byte[]";
                    break;
                default:
                    str = $"object";
                    break;
            }
            lvt.Content = $@"        public {str} {dr["fieldName"]} {{ get; set; }}";
            return lvt;
        }


        #region 右键菜单

        /// <summary>
        /// 复制
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void MenuItem_Copy_Click(object sender, RoutedEventArgs e)
        {
            if (LvEntityView.SelectedItems.Count == 0)
                return;

            try
            {
                string copyStr = "";
                foreach (var item in LvEntityView.SelectedItems)
                {
                    string str = ((ContentControl)item).Content.ToString();
                    if (!string.IsNullOrWhiteSpace(str))
                        copyStr += str + "\n";
                }
                SafeClipboard.SetDataAtomic(DataFormats.Text, copyStr); // 自动处理冲突
                //Clipboard.SetData(DataFormats.Text, copyStr);
            }
            catch (Exception ex)
            {
                if (sender == null)
                    MessageBox.Show("粘贴板异常！原因：" + ex.Message, "提示", MessageBoxButton.OK);
                else
                {
                    Thread.Sleep(300); // 等待一段时间后重试
                    MenuItem_AllCopy_Click(null, null);
                }
            }
        }

        /// <summary>
        /// 复制全部
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void MenuItem_AllCopy_Click(object sender, RoutedEventArgs e)
        {
            if (LvEntityView.Items.Count == 0)
                return;
            try
            {
                string copyStr = "";
                foreach (var item in LvEntityView.Items)
                {
                    string str = ((ContentControl)item).Content.ToString();
                    copyStr += str + "\n";
                }

                SafeClipboard.SetDataAtomic(DataFormats.Text, copyStr); // 自动处理冲突
                //Clipboard.SetData(DataFormats.Text, copyStr);
            }
            catch (Exception ex)
            {
                if (sender == null)
                    MessageBox.Show("粘贴板异常！原因：" + ex.Message, "提示", MessageBoxButton.OK);
                else
                {
                    Thread.Sleep(300); // 等待一段时间后重试
                    MenuItem_AllCopy_Click(null, null);
                }
            }

        }

        #endregion

    }
}
